package com.xdwanj.dao.impl;

import com.mysql.jdbc.StringUtils;
import com.xdwanj.dao.BaseDao;
import com.xdwanj.dao.CommodityDao;
import com.xdwanj.pojo.Category;
import com.xdwanj.pojo.Commodity;
import com.xdwanj.pojo.Picture;
import com.xdwanj.util.PageBean;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class CommodityDaoImpl implements CommodityDao {
    private BaseDao baseDao = new BaseDao();
    JdbcTemplate jdbcTemplate = baseDao.getJdbcTemplate();

    @Override
    public List<Commodity> findAll() throws Exception {
        String sql = "select * from commodity";
        List<Commodity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Commodity>(Commodity.class));
        return list;
    }

    @Override
    public int add(Commodity commodity, int catId) throws Exception {
        String sql = "insert into commodity (name,price,cat_id,manufacture_date,expiration_date,sales_num,info) values(?,?,?,?,?,?,?)";
        String CCSql = "insert into commodity_category (ca_id) values(?)";
        int addRow = jdbcTemplate.update(sql, commodity.getName(), catId, commodity.getPrice(), commodity.getManufactureDate(), commodity.getExpirationDate(), commodity.getSalesNum(), commodity.getInfo());
        addRow = addRow + jdbcTemplate.update(CCSql, catId);
        return addRow;
    }

    @Override
    public int delete(int id) throws Exception {
        String sql = "delete from commodity where co_id = ?";
        String CCSql = "delete from commodity_category where co_id = ? ";
        int delRow = jdbcTemplate.update(sql, id);
        delRow += jdbcTemplate.update(CCSql, id);
        return delRow;
    }

    @Override
    public int updateById(Commodity commodity, int catId) throws Exception {
        String sql = "update commodity set name = ?,price = ?,manufacture_date = ?,expiration_date = ?,sales_num = ?,info = ?,cat_id = ? where id = ?";
        String CCSql = "update commodity_category set ca_id = ? where co_id = ?";
        int i = jdbcTemplate.update(sql, commodity.getName(), commodity.getPrice(), commodity.getManufactureDate(), commodity.getExpirationDate(), commodity.getSalesNum(), commodity.getInfo(), catId, commodity.getId());
        i = i + jdbcTemplate.update(CCSql, catId, commodity.getId());
        return i;
    }


    @Override
    public PageBean<Commodity> findAllByCommodityAndCategory(String name, int pageSize, int currentPageNo, int categoryId) throws Exception {
        StringBuffer sql = new StringBuffer(" , commodity_category cc where co.cat_id = cc.ca_id and cc.ca_id = ca.id AND co.id=cc.co_id  ");
        List<Object> params = new ArrayList<>();
        if (categoryId != 0) {
            sql.append("and ca.id = ?");
            params.add(categoryId);
        }
        System.out.println(params);
        if (!StringUtils.isNullOrEmpty(name)) {
            name = name.trim();
            StringBuffer nameStrBuffer = new StringBuffer(name);
            for (int i = 0; i < name.length(); i++) {
                if (name.charAt(i) == ' ') {
                    nameStrBuffer.setCharAt(i, '%');
                }
            }
            sql.append(" and co.name like '%").append(nameStrBuffer).append("%'");
        }
        System.out.println(new StringBuffer("select *,co.id commodity_id from category ca, commodity co").append(sql));
        System.out.println(new StringBuffer("select count(*)").append(sql));

        PageBean<Commodity> customerPageDto = baseDao.getCustomerPageDto(
                new StringBuffer("select *,co.id commodity_id from  category ca, commodity co").append(sql).append(" ORDER BY sales_num desc").toString(),
                params.toArray(),
                new BeanPropertyRowMapper<Commodity>(Commodity.class),
                new StringBuffer("select count(*) from  category ca, commodity co").append(sql).toString(),
                params.toArray(),
                currentPageNo,
                pageSize);
        System.out.println(customerPageDto);
        System.out.println(new StringBuffer("SELECT *,cc.co_id commodity_id FROM picture p, commodity co ,category ca").append(sql));
        List<Category> categories = jdbcTemplate.query(
                new StringBuffer("SELECT *,cc.co_id commodity_id FROM commodity co ,category ca ").append(sql).toString(),
                new BeanPropertyRowMapper<Category>(Category.class), params.toArray());
        List<Picture> pictures = jdbcTemplate.query(new StringBuffer("SELECT *,cc.co_id commodity_id FROM commodity_picture cp, commodity co ,category ca,picture p ").append(sql).append(" AND co.id = cp.c_id AND cp.c_id = p.c_id AND cp.p_id = p.id").toString(),
                new BeanPropertyRowMapper<Picture>(Picture.class), params.toArray());
        List<Commodity> commodities = customerPageDto.getContent();
        System.out.println(new StringBuffer("SELECT *,cc.co_id commodity_id FROM commodity_picture cp, commodity co ,category ca,picture p ").append(sql).append(" AND co.id = cp.c_id AND cp.c_id = p.c_id AND cp.p_id = p.id"));
        System.out.println(pictures);
        for (Commodity commodity : commodities) {
            for (int i = 0; i < categories.size(); i++) {
                if (categories.get(i).getCommodityId().equals(commodity.getId())) {
                    commodity.setCategory(categories.get(i));
                }
            }
            List<Picture> pictureList = new ArrayList<>();
            for (int i = 0; i < pictures.size(); i++) {
                if (pictures.get(i).getCommodityId().equals(commodity.getId())) {
                    pictureList.add(pictures.get(i));
                }
                commodity.setPictureList(pictureList);
            }
        }

        if (name != null && name.length() != 0) {
            Collections.sort(commodities, (name1, name2) -> {
                int result = name1.getName().length() - name2.getName().length();
                return result;
            });
        }
        return customerPageDto;
    }


    @Override
    public Commodity findAllById(int id) throws Exception {
        StringBuffer sql = new StringBuffer(" , commodity_category cc where co.cat_id = cc.ca_id and cc.ca_id = ca.id AND co.id=cc.co_id and co.id = ? ");
        List<Object> params = new ArrayList<>();
        params.add(id);
        Commodity commodity = jdbcTemplate.queryForObject(new StringBuffer("select *,co.id commodity_id from  category ca, commodity co").append(sql).toString(), new BeanPropertyRowMapper<Commodity>(Commodity.class), params.toArray());
        List<Picture> pictures = jdbcTemplate.query(new StringBuffer("SELECT *,cc.co_id commodity_id FROM commodity_picture cp, commodity co ,category ca,picture p ").append(sql).append(" AND co.id = cp.c_id AND cp.c_id = p.c_id AND cp.p_id = p.id").toString(), new BeanPropertyRowMapper<Picture>(Picture.class), params.toArray());
        System.out.println(new StringBuffer("SELECT *,cc.co_id commodity_id FROM commodity_picture cp, commodity co ,category ca,picture p ").append(sql).append(" AND co.id = cp.c_id AND cp.c_id = p.c_id AND cp.p_id = p.id"));
        List<Category> categories = jdbcTemplate.query(
                new StringBuffer("SELECT *,cc.co_id commodity_id FROM commodity_picture cp, picture p, commodity co ,category ca ").append(sql).toString(),
                new BeanPropertyRowMapper<Category>(Category.class), params.toArray());
        for (int i = 0; i < categories.size(); i++) {
            if (categories.get(i).getCommodityId().equals(commodity.getId())) {
                commodity.setCategory(categories.get(i));
            }
        }
        List<Picture> pictureList = new ArrayList<>();
        for (int i = 0; i < pictures.size(); i++) {
            if (pictures.get(i).getCommodityId().equals(commodity.getId())) {
                pictureList.add(pictures.get(i));
            }
        }
        commodity.setPictureList(pictureList);
        return commodity;
    }

}
